﻿using Newtonsoft.Json;
using OfficeOpenXml;
using System.Data;
using System.IO;

namespace Compass.Wpf.Extensions;

public static class EPPlusExtensions
{
    public static T ReadFromExcel<T>(this string path, bool hasHeader = true)
    {
        using (var excelPack = new ExcelPackage())
        {
            //Load excel stream
            using (var stream = File.OpenRead(path))
            {
                excelPack.Load(stream);
            }

            //Lets Deal with first worksheet.(You may iterate here if dealing with multiple sheets)
            var ws = excelPack.Workbook.Worksheets[0];

            //Get all details as DataTable -because Datatable make life easy :)
            DataTable excelasTable = new DataTable();
            foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
            {
                //Get colummn details
                if (!string.IsNullOrEmpty(firstRowCell.Text))
                {
                    string firstColumn = string.Format("Column {0}", firstRowCell.Start.Column);
                    excelasTable.Columns.Add(hasHeader ? firstRowCell.Text : firstColumn);
                }
            }
            var startRow = hasHeader ? 2 : 1;
            //Get row details
            for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
            {
                var wsRow = ws.Cells[rowNum, 1, rowNum, excelasTable.Columns.Count];
                DataRow row = excelasTable.Rows.Add();
                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
            }
            //Get everything as generics and let end user decides on casting to required type
            var generatedType = JsonConvert.DeserializeObject<T>(JsonConvert.SerializeObject(excelasTable));
            return (T)Convert.ChangeType(generatedType, typeof(T));
        }
    }
    
    public static void WriteExcel(this object? value, string tablePath, string tableName)
    {
        // let's convert our object data to Datatable for a simplified logic.
        // Datatable is the easiest way to deal with complex datatypes for easy reading and formatting.
        var table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(value), typeof(DataTable))!;
        using var excelPack = new ExcelPackage(tablePath);
        var ws = excelPack.Workbook.Worksheets.Add(tableName);
        //https://blog.csdn.net/cxb2011/article/details/108483881
        ws.Cells.LoadFromDataTable(table, true, OfficeOpenXml.Table.TableStyles.Light8);
        excelPack.Save();
    }

}